package com.ccxc.question.exam.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.ccxc.question.exam.dto.DailyDTO;
import com.ccxc.question.exam.dto.OperationDTO;
import com.ccxc.question.exam.dto.RecordDTO;
import com.ccxc.question.exam.dto.SummaryDTO;


@Component
public class OperationDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * @DEscription 功能说明  获取题目答案
     * 参数说明：queid是题目ID
     */
    public String contrast(String queid) {
        final String sql = "SELECT ANSWER,KNOWLEDGE_AREA FROM t_question WHERE ID = ?";
        List<OperationDTO> list = jdbcTemplate.query(sql, new Object[]{queid}, new RowMapper<OperationDTO>() {
            public OperationDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                OperationDTO operation = new OperationDTO();
                operation.setAnswer(rs.getString("ANSWER"));
                operation.setArea(rs.getString("KNOWLEDGE_AREA"));
                return operation;
            }
        });

        if (null != list && list.size() > 0) {
            return list.get(0).getAnswer();
        }
        return null;
    }

    /**
     * @DEscription 功能说明 获取题目知识领域
     * 参数说明： queid是题目ID
     */
    public String contrast2(String queid) {
        final String sql = "SELECT ANSWER,KNOWLEDGE_AREA FROM t_question WHERE ID = ?";
        List<OperationDTO> list = jdbcTemplate.query(sql, new Object[]{queid}, new RowMapper<OperationDTO>() {
            public OperationDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                OperationDTO operation = new OperationDTO();
                operation.setAnswer(rs.getString("ANSWER"));
                operation.setArea(rs.getString("KNOWLEDGE_AREA"));
                return operation;
            }
        });

        if (null != list && list.size() > 0) {
            return list.get(0).getArea();
        }
        return null;
    }

    /**
     * @DEscription 功能说明 判断每日答题表是否存在
     * 参数说明： id是用户ID
     */
    public boolean judge(String id) {
        RowMapper<DailyDTO> rowMapper = new BeanPropertyRowMapper<DailyDTO>(DailyDTO.class);
        final String sql = "SELECT * FROM t_daily_statistics WHERE DATE(`TIME`) = CURDATE() AND USER_ID = ?;";
        List<DailyDTO> list = jdbcTemplate.query(sql, rowMapper, id);

        if (0 == list.size()) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * @DEscription 功能说明 判断答题汇总表是否存在
     * 参数说明：id是用户ID
     */
    public boolean judge2(String id) {
        RowMapper<SummaryDTO> rowMapper = new BeanPropertyRowMapper<SummaryDTO>(SummaryDTO.class);
        final String sql = "SELECT * FROM t_summary_statistics WHERE USER_ID = ?;";
        List<SummaryDTO> list = jdbcTemplate.query(sql, rowMapper, id);

        if (0 == list.size()) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * @DEscription 功能说明 如果答题正确加分，修改每日答题表数据
     * 参数说明： id是用户ID
     */
    public String bonus_points(String id) {

        final String sql = "UPDATE t_daily_statistics SET CORRECT_NUMBER = CORRECT_NUMBER + 1, ERROR_NUMBER = ERROR_NUMBER + 0, TOTAL_NUMBER = TOTAL_NUMBER + 1, CURRENT_SCORE = CURRENT_SCORE + 10, THE_DAY_SCORE = THE_DAY_SCORE + 10 WHERE	DATE(`TIME`) = CURDATE() AND USER_ID = ?";
        DailyDTO dto = new DailyDTO();
        dto.setUserid(id);
        jdbcTemplate.update(sql,
                new Object[]{dto.getUserid()},
                new int[]{Types.VARCHAR}
        );
        return "成功";
    }

    /**
     * @DEscription 功能说明 如果答题错误不加分，修改每日答题表数据
     * 参数说明： id是用户ID
     */
    public String points(String id) {

        final String sql = " UPDATE t_daily_statistics SET CORRECT_NUMBER = CORRECT_NUMBER + 0, ERROR_NUMBER = ERROR_NUMBER + 1, TOTAL_NUMBER = TOTAL_NUMBER + 1, CURRENT_SCORE = CURRENT_SCORE + 0, THE_DAY_SCORE = THE_DAY_SCORE + 0 WHERE	DATE(`TIME`) = CURDATE() AND USER_ID = ?";
        DailyDTO dto = new DailyDTO();
        dto.setUserid(id);
        jdbcTemplate.update(sql,
                new Object[]{dto.getUserid()},
                new int[]{Types.VARCHAR}
        );
        return "成功";
    }

    /**
     * @DEscription 功能说明 在判断每日答题表时，如果不存在就建立一共空数据的行
     * 参数说明： queid是题目ID，userid 是用户ID
     */
    public String build(String queid, String userid) {

        final String sql = "INSERT INTO  t_daily_statistics (`USER_ID`,CORRECT_NUMBER, ERROR_NUMBER, TOTAL_NUMBER,CURRENT_SCORE,THE_DAY_SCORE,TIME) VALUES (?, 0, 0, 0, 0, 0, NOW())";
        DailyDTO dailyDto = new DailyDTO();
        dailyDto.setUserid(userid);
        jdbcTemplate.update(sql, dailyDto.getUserid());
        return "成功";
    }

    /**
     * @DEscription 功能说明 每做一题，都需要添加一条答题记录
     * 参数说明：queid是题目ID，answer是提交的答案，i是判断正误的标准，userid是用户ID，area是知识领域，z_answer是正确答案
     */
    public void record(String queid, String answer, int i, String userid, String area, String z_answer) {
        final String sql = "INSERT INTO  t_record (`QUESTION_ID`,SUBMIT_ANSWER, JUDGE, USER_ID,CORRECT_ANSWER,TIME,THE_KNOWLEDGE_AREA,SCORE) VALUES (?, ?, ?, ?, ?, NOW(), ?, ?)";
        RecordDTO dto = new RecordDTO();
        dto.setQueid(queid);
        dto.setSu_answer(answer);
        dto.setJudge(i);
        dto.setScore(i * 5);
        dto.setCo_answer(z_answer);
        dto.setUserid(userid);
        dto.setArea(area);
        jdbcTemplate.update(sql, dto.getQueid(), dto.getSu_answer(), dto.getJudge(), dto.getUserid(), dto.getCo_answer(), dto.getArea(), dto.getScore());
    }

    /**
     * @DEscription 功能说明 在判断答题汇总表时，如果不存在就建立一共空数据的行
     * 参数说明：userid是用户ID
     */
    public String build2(String userid) {

        final String sql = "INSERT INTO  t_summary_statistics (`USER_ID`,CUMULATIVE_TOTAL_SCORE, CUMULATIVE_CORRECT_NUMBER, CUMULATIVE_ERROR_NUMBER,CUMULATIVE_TOTAL_NUMBER) VALUES (?, 0, 0, 0, 0)";
        SummaryDTO summaryDto = new SummaryDTO();
        summaryDto.setUserid(userid);
        jdbcTemplate.update(sql, summaryDto.getUserid());
        return "成功";

    }

    /**
     * @DEscription 功能说明 如果答题正确，修改答题汇总表数据
     * 参数说明： id是用户ID
     */
    public String correct(String id) {
        final String sql = "UPDATE t_summary_statistics SET CUMULATIVE_CORRECT_NUMBER = CUMULATIVE_CORRECT_NUMBER + 1, CUMULATIVE_ERROR_NUMBER = CUMULATIVE_ERROR_NUMBER + 0, CUMULATIVE_TOTAL_NUMBER = CUMULATIVE_TOTAL_NUMBER + 1, CUMULATIVE_TOTAL_SCORE = CUMULATIVE_TOTAL_SCORE + 10  WHERE	 USER_ID = ?";
        SummaryDTO dto = new SummaryDTO();
        dto.setUserid(id);
        jdbcTemplate.update(sql,
                new Object[]{dto.getUserid()},
                new int[]{Types.VARCHAR}
        );
        return "成功";
    }

    /**
     * @DEscription 功能说明 如果答题错误，修改答题汇总表数据
     * 参数说明：id是用户ID
     */
    public String error(String id) {
        final String sql = "UPDATE t_summary_statistics SET CUMULATIVE_CORRECT_NUMBER = CUMULATIVE_CORRECT_NUMBER + 0, CUMULATIVE_ERROR_NUMBER = CUMULATIVE_ERROR_NUMBER + 1, CUMULATIVE_TOTAL_NUMBER = CUMULATIVE_TOTAL_NUMBER + 1, CUMULATIVE_TOTAL_SCORE = CUMULATIVE_TOTAL_SCORE + 0  WHERE	 USER_ID = ?";
        SummaryDTO dto = new SummaryDTO();
        dto.setUserid(id);
        jdbcTemplate.update(sql,
                new Object[]{dto.getUserid()},
                new int[]{Types.VARCHAR}
        );
        return "成功";
    }

    /**
     * @DEscription 功能说明 判断分数是否为空
     * 参数说明：
     */
    public boolean judgescore(String id) {
        RowMapper<SummaryDTO> rowMapper = new BeanPropertyRowMapper<SummaryDTO>(SummaryDTO.class);
        final String sql = "SELECT CUMULATIVE_TOTAL_SCORE FROM t_summary_statistics WHERE USER_ID = ?";
        List<SummaryDTO> list = jdbcTemplate.query(sql, rowMapper, id);

        if (list.get(0).getScore() <= 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * @DEscription 功能说明  答错减分 每日答题记录
     * 参数说明：
     */
    public String points2(String id) {
        final String sql = " UPDATE t_daily_statistics SET CORRECT_NUMBER = CORRECT_NUMBER + 0, ERROR_NUMBER = ERROR_NUMBER + 1, TOTAL_NUMBER = TOTAL_NUMBER + 1, CURRENT_SCORE = CURRENT_SCORE - 3, THE_DAY_SCORE = THE_DAY_SCORE - 3 WHERE	DATE(`TIME`) = CURDATE() AND USER_ID = ?";
        DailyDTO dto = new DailyDTO();
        dto.setUserid(id);
        jdbcTemplate.update(sql,
                new Object[]{dto.getUserid()},
                new int[]{Types.VARCHAR}
        );
        return "成功";
    }

    /**
     * @DEscription 功能说明 答错减分。修改答题汇总表
     * 参数说明：
     */
    public String error2(String id) {
        final String sql = "UPDATE t_summary_statistics SET CUMULATIVE_CORRECT_NUMBER = CUMULATIVE_CORRECT_NUMBER + 0, CUMULATIVE_ERROR_NUMBER = CUMULATIVE_ERROR_NUMBER + 1, CUMULATIVE_TOTAL_NUMBER = CUMULATIVE_TOTAL_NUMBER + 1, CUMULATIVE_TOTAL_SCORE = CUMULATIVE_TOTAL_SCORE - 3  WHERE	 USER_ID = ?";
        SummaryDTO dto = new SummaryDTO();
        dto.setUserid(id);
        jdbcTemplate.update(sql,
                new Object[]{dto.getUserid()},
                new int[]{Types.VARCHAR}
        );
        return "成功";
    }

	public boolean judge3(String userid) {
		 RowMapper<OperationDTO> rowMapper = new BeanPropertyRowMapper<OperationDTO>(OperationDTO.class);
		final String sql = "SELECT `ID` FROM t_user_information WHERE ID = ?";
		 List<OperationDTO> list = jdbcTemplate.query(sql, rowMapper,userid);
		 if ( 0 == list.size()) {
	            return false;
	        } else {
	            return true;
	        }
	}
}

